import { sql, normalizePaginationParams } from "#/utils";
import type { RentBill } from "#/types";

export default {
  insert(data: RentBill) {
    return sql` 
   INSERT INTO t_rent_bills
      (
      id, 
      month, 
      water_current , 
      water_last,
      water_price,
      electric_current,
      electric_last,
      electric_price,
      rent,clean_fee,
      description,
      settle_utilities,
      created_time,
      \`rank\`
      ) 
    VALUES 
    (
      ${data.id},
      ${data.month},
      ${data.water_current},
      ${data.water_last},
      ${data.water_price},
      ${data.electric_current},
      ${data.electric_last},
      ${data.electric_price},
      ${data.rent},
      ${data.clean_fee},
      ${data.description},
      ${data.settle_utilities},
      ${data.created_time},
      ${data.rank}
)
`;
  },
  deleteById(id: string) {
    return sql` DELETE FROM t_rent_bills WHERE id = ${id}`;
  },
  update(data: RentBill) {
    return sql`UPDATE  SET name = ?, color = ? WHERE id = ?`;
  },
  queryList(pageInfo): Promise<RentBill[]> {
    const { page, pageSize, date, settle_utilities } =
      normalizePaginationParams(pageInfo);
    if (date) {
      const { year, month } = this.splitYearMonthDay(date);
      return sql` 
    SELECT * FROM t_rent_bills
      WHERE YEAR(month) = ${year}
      AND MONTH(month) = ${month}`;
    }
    if (settle_utilities) {
      return sql` 
    SELECT * FROM t_rent_bills
      WHERE settle_utilities = ${settle_utilities}
      ORDER BY \`rank\` DESC LIMIT ${pageSize} 
      OFFSET ${(page - 1) * pageSize}
      `;
    }
    return sql` 
    SELECT * FROM t_rent_bills 
      ORDER BY \`rank\` DESC LIMIT ${pageSize} 
      OFFSET ${(page - 1) * pageSize}`;
  },

  queryTotalByStatus(settle_utilities): Promise<{ total: number }[]> {
    return sql`SELECT COUNT(*) AS total FROM t_rent_bills WHERE settle_utilities = ${settle_utilities}`;
  },

  queryTotal(): Promise<{ total: number }[]> {
    return sql`SELECT COUNT(*) AS total FROM t_rent_bills`;
  },
  getMaxRank(): Promise<{ maxRank: number }[]> {
    return sql`SELECT MAX(\`rank\`) AS maxRank FROM t_rent_bills`;
  },
  queryRentTotal() {
    return sql`
  SELECT
    SUM(electric_usage) AS total_electric_usage,
    SUM(water_usage) AS total_water_usage,
    SUM(electric_fee) AS total_electric_fee,
    SUM(water_fee) AS total_water_fee,
    SUM(rent) AS total_rent,
    SUM(total) AS total_amount 
  FROM t_rent_bills;
`;
  },
  splitYearMonthDay(monthString) {
    const [year, month, day] = monthString.split("-");
    return {
      year: parseInt(year, 10), // 转为数字
      month: parseInt(month, 10), // 转为数字
      day: parseInt(day, 10), // 转为数字
    };
  },
};
